/********************************************************************
FileName    :   Dal_cktz.cs
CreateTime  :   2015-10-15 10:10
BaseClass   :   Dal_cktz
FileType    :   .cs
Author      :  minjie
Description :   Dal_cktzDAL层（简单三层）
*********************************************************************/

using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;

using System.Data;
namespace OS.DAL
{
    /// <summary>
    /// Dal_cktz的摘要说明。
    /// </summary>
    public partial class Dal_cktz
    {

        public bool Exists(string bmh)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from cktz");
            strSql.Append(" where ");
            SqlParameter[] parameters = { new SqlParameter("@bmh", SqlDbType.Int, 4) };
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }



        /// <summary> 
        /// 增加一条数据 
        /// </summary> 
        public bool Add(cktz_Entity model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into cktz(");
            strSql.Append("rq,bmh,pzh,spbh,sl,dj,czy,mem,cklx,xh,cgd_pzh,bj,csbj) values (");
            strSql.Append("	@rq,@bmh,@pzh,@spbh,@sl,@dj,@czy,@mem,@cklx,@xh,@cgd_pzh,@bj,@csbj	 ) ");
            SqlParameter[] parameters = { 
              new SqlParameter("@rq", SqlDbType.DateTime) ,              
              new SqlParameter("@bmh", SqlDbType.Char,10) ,              
              new SqlParameter("@pzh", SqlDbType.Char,10) ,              
              new SqlParameter("@spbh", SqlDbType.Char,10) ,              
              new SqlParameter("@sl", SqlDbType.Decimal,9) ,              
              new SqlParameter("@dj", SqlDbType.Decimal,9) ,              
              new SqlParameter("@czy", SqlDbType.Char,10) ,              
              new SqlParameter("@mem", SqlDbType.Char,10) ,              
              new SqlParameter("@cklx", SqlDbType.Char,10) ,              
              new SqlParameter("@xh", SqlDbType.Char,10) ,              
              new SqlParameter("@cgd_pzh", SqlDbType.Char,10) ,              
              new SqlParameter("@bj", SqlDbType.Int,4) ,              
              new SqlParameter("@csbj", SqlDbType.Int,4)};
            if (model.rq != null)
                parameters[0].Value = model.rq;
            else
                parameters[0].Value = DBNull.Value;

            if (model.bmh != null)
                parameters[1].Value = model.bmh;
            else
                parameters[1].Value = DBNull.Value;

            if (model.pzh != null)
                parameters[2].Value = model.pzh;
            else
                parameters[2].Value = DBNull.Value;

            if (model.spbh != null)
                parameters[3].Value = model.spbh;
            else
                parameters[3].Value = DBNull.Value;

            if (model.sl != null)
                parameters[4].Value = model.sl;
            else
                parameters[4].Value = DBNull.Value;

            if (model.dj != null)
                parameters[5].Value = model.dj;
            else
                parameters[5].Value = DBNull.Value;

            if (model.czy != null)
                parameters[6].Value = model.czy;
            else
                parameters[6].Value = DBNull.Value;

            if (model.mem != null)
                parameters[7].Value = model.mem;
            else
                parameters[7].Value = DBNull.Value;

            if (model.cklx != null)
                parameters[8].Value = model.cklx;
            else
                parameters[8].Value = DBNull.Value;

            if (model.xh != null)
                parameters[9].Value = model.xh;
            else
                parameters[9].Value = DBNull.Value;

            if (model.cgd_pzh != null)
                parameters[10].Value = model.cgd_pzh;
            else
                parameters[10].Value = DBNull.Value;

            if (model.bj != null)
                parameters[11].Value = model.bj;
            else
                parameters[11].Value = DBNull.Value;

            if (model.csbj != null)
                parameters[12].Value = model.csbj;
            else
                parameters[12].Value = DBNull.Value;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false; ;
            }
        }

        /// <summary> 
        /// 增加一条数据(字符串) 
        /// </summary> 
        public string AddStr(cktz_Entity model)
        {
            if (model.bmh == null)
            {
                model.bmh = "";
            }
            if (model.pzh == null)
            {
                model.pzh = "";
            }
            if (model.spbh == null)
            {
                model.spbh = "";
            }
            if (model.sl == null)
            {
                model.sl = 0;
            }
            if (model.dj == null)
            {
                model.dj = 0;
            }
            if (model.czy == null)
            {
                model.czy = "";
            }
            if (model.mem == null)
            {
                model.mem = "";
            }
            if (model.cklx == null)
            {
                model.cklx = "";
            }
            if (model.xh == null)
            {
                model.xh = "";
            }
            if (model.cgd_pzh == null)
            {
                model.cgd_pzh = "";
            }
            if (model.bj == null)
            {
                model.bj = 0;
            }
            if (model.csbj == null)
            {
                model.csbj = 0;
            }

            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into cktz(");
            strSql.Append("rq,bmh,pzh,spbh,sl,dj,czy,mem,cklx,xh,cgd_pzh,bj,csbj) values (");
            strSql.Append("	'" + model.rq + "','" + model.bmh + "','" + model.pzh + "','" + model.spbh + "'," + model.sl + "," + model.dj + ",'" + model.czy + "','" + model.mem + "','" + model.cklx + "','" + model.xh + "','" + model.cgd_pzh + "'," + model.bj + "," + model.csbj + "	 ) ");
            return strSql.ToString();
        }


        /// <summary> 
        /// 更新一条数据 
        /// </summary> 
        public bool Update(cktz_Entity model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update cktz set ");
            strSql.Append(" rq = @rq,");
            strSql.Append(" bmh = @bmh,");
            strSql.Append(" pzh = @pzh,");
            strSql.Append(" spbh = @spbh,");
            strSql.Append(" sl = @sl,");
            strSql.Append(" dj = @dj,");
            strSql.Append(" czy = @czy,");
            strSql.Append(" mem = @mem,");
            strSql.Append(" cklx = @cklx,");
            strSql.Append(" xh = @xh,");
            strSql.Append(" cgd_pzh = @cgd_pzh,");
            strSql.Append(" bj = @bj,");
            strSql.Append(" csbj = @csbj");
            strSql.Append(" where  bmh=@bmh ");

            SqlParameter[] parameters = { 
              new SqlParameter("@rq", SqlDbType.DateTime) ,              
              new SqlParameter("@bmh", SqlDbType.Char,10) ,              
              new SqlParameter("@pzh", SqlDbType.Char,10) ,              
              new SqlParameter("@spbh", SqlDbType.Char,10) ,              
              new SqlParameter("@sl", SqlDbType.Decimal,9) ,              
              new SqlParameter("@dj", SqlDbType.Decimal,9) ,              
              new SqlParameter("@czy", SqlDbType.Char,10) ,              
              new SqlParameter("@mem", SqlDbType.Char,10) ,              
              new SqlParameter("@cklx", SqlDbType.Char,10) ,              
              new SqlParameter("@xh", SqlDbType.Char,10) ,              
              new SqlParameter("@cgd_pzh", SqlDbType.Char,10) ,              
              new SqlParameter("@bj", SqlDbType.Int,4) ,              
              new SqlParameter("@csbj", SqlDbType.Int,4)};
            if (model.rq != null)
                parameters[0].Value = model.rq;
            else
                parameters[0].Value = DBNull.Value;

            if (model.bmh != null)
                parameters[1].Value = model.bmh;
            else
                parameters[1].Value = DBNull.Value;

            if (model.pzh != null)
                parameters[2].Value = model.pzh;
            else
                parameters[2].Value = DBNull.Value;

            if (model.spbh != null)
                parameters[3].Value = model.spbh;
            else
                parameters[3].Value = DBNull.Value;

            if (model.sl != null)
                parameters[4].Value = model.sl;
            else
                parameters[4].Value = DBNull.Value;

            if (model.dj != null)
                parameters[5].Value = model.dj;
            else
                parameters[5].Value = DBNull.Value;

            if (model.czy != null)
                parameters[6].Value = model.czy;
            else
                parameters[6].Value = DBNull.Value;

            if (model.mem != null)
                parameters[7].Value = model.mem;
            else
                parameters[7].Value = DBNull.Value;

            if (model.cklx != null)
                parameters[8].Value = model.cklx;
            else
                parameters[8].Value = DBNull.Value;

            if (model.xh != null)
                parameters[9].Value = model.xh;
            else
                parameters[9].Value = DBNull.Value;

            if (model.cgd_pzh != null)
                parameters[10].Value = model.cgd_pzh;
            else
                parameters[10].Value = DBNull.Value;

            if (model.bj != null)
                parameters[11].Value = model.bj;
            else
                parameters[11].Value = DBNull.Value;

            if (model.csbj != null)
                parameters[12].Value = model.csbj;
            else
                parameters[12].Value = DBNull.Value;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary> 
        /// 删除一条数据 
        /// </summary> 
        public bool Delete(string bmh)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from cktz ");
            strSql.Append(" where  bmh=@bmh");
            SqlParameter[] parameters = { new SqlParameter("@bmh", SqlDbType.Char, 10) };
            parameters[0].Value = bmh;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary> 
        /// 批量删除一批数据 
        /// </summary> 
        public bool DeleteList(string bmhlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from cktz ");
            strSql.Append(" where ID in (" + bmhlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary> 
        /// 得到一个对象实体 
        /// </summary> 
        public cktz_Entity GetModel(string bmh)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  rq,bmh,pzh,spbh,sl,dj,czy,mem,cklx,xh,cgd_pzh,bj,csbj");
            strSql.Append("  from cktz");
            strSql.Append(" where  bmh=@bmh ");
            SqlParameter[] parameters = { new SqlParameter("@bmh", SqlDbType.Char, 10) };
            parameters[0].Value = bmh;

            cktz_Entity model = new cktz_Entity();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                if (dt.Rows[0]["rq"] != null && dt.Rows[0]["rq"].ToString() != "")
                {
                    model.rq = DateTime.Parse(dt.Rows[0]["rq"].ToString());
                }
                model.bmh = dt.Rows[0]["bmh"].ToString();
                model.pzh = dt.Rows[0]["pzh"].ToString();
                model.spbh = dt.Rows[0]["spbh"].ToString();
                if (dt.Rows[0]["sl"] != null && dt.Rows[0]["sl"].ToString() != "")
                {
                    model.sl = decimal.Parse(dt.Rows[0]["sl"].ToString());
                }
                if (dt.Rows[0]["dj"] != null && dt.Rows[0]["dj"].ToString() != "")
                {
                    model.dj = decimal.Parse(dt.Rows[0]["dj"].ToString());
                }
                model.czy = dt.Rows[0]["czy"].ToString();
                model.mem = dt.Rows[0]["mem"].ToString();
                model.cklx = dt.Rows[0]["cklx"].ToString();
                model.xh = dt.Rows[0]["xh"].ToString();
                model.cgd_pzh = dt.Rows[0]["cgd_pzh"].ToString();
                if (dt.Rows[0]["bj"] != null && dt.Rows[0]["bj"].ToString() != "")
                {
                    model.bj = int.Parse(dt.Rows[0]["bj"].ToString());
                }
                if (dt.Rows[0]["csbj"] != null && dt.Rows[0]["csbj"].ToString() != "")
                {
                    model.csbj = int.Parse(dt.Rows[0]["csbj"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary> 
        /// 分页获取数据列表 
        /// </summary> 
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.bmh desc");
            }
            strSql.Append(")AS Row, T.*  from cktz T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex); return DbHelperSQL.Query(strSql.ToString());
        }
        public DataSet GetListByPage(int pageIndex, int pageSize,string strWhere)
        {
            int oldPage = (pageIndex - 1) * pageSize;

            string strWhere1 = "";
            string strWhere2 = "";
            if (strWhere != "")
            {
                strWhere1 = " where "+strWhere;
                strWhere2 = " and "+strWhere; 
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top " + pageSize + " *from cktz ");
            strSql.Append("where id not in (select top " + oldPage + " id from cktz " + strWhere1 + "  order by id)   " + strWhere2 + " order by id  ");
            return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary> 
        /// 获得数据列表 
        /// </summary> 
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM cktz ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary> 
        /// 获得前几行数据 
        /// </summary> 
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM cktz ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary> 
        /// 获得记录条数 
        /// </summary> 
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) ");
            strSql.Append(" FROM cktz ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj != null && obj.ToString() != "")
            {
                return Convert.ToInt32(obj);
            }
            else
                return 0;
        }

        public int Update(List<cktz_Entity> ls)
        {
            int count = 0;
            if (ls.Count > 0)
            {
                List<string> sqlList = new List<string>();
                foreach (cktz_Entity entity in ls)
                {
                    string sql = "update cktz  set csbj=1 where convert(varchar(30),rq,120)='" + entity.rq2 + "' and bmh='" + entity.bmh + "' and pzh='" + entity.pzh + "' and spbh ='" + entity.spbh + "'";
                    sqlList.Add(sql);
                }


                if (sqlList.Count > 0)
                    count = DbHelperSQL.ExecuteSqlTran(sqlList);
            }
            return count;
        }


    }

}
